package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;

import bean.Article;
import bean.DownloadArticle;
import bean.LineItem;
import bean.Order;
import bean.WarehouseArticle;
import bean.exceptions.PositionIdTakenException;
import exceptions.IdNotFoundException;

/**
 * Handles the database access for the order data
 * @author Martin Schophaus
 *
 */
public class DB_Order extends Handler implements HandlerInterface<Order> {

	public DB_Order(Connection db) {
		super(db);
	}

	@Override
	public Order getById(int id) throws SQLException, IdNotFoundException {
		
		// Get Order and Order Status
		String sql = "SELECT x.order_id, x.customer_id, x.order_status_id, x.delivery_charges, x.calendar_date, y.name" +
				" FROM orders x" +
				" LEFT JOIN order_status y ON (x.order_status_id = y.order_status_id)" +
				" WHERE order_id = ?";
		
		PreparedStatement stmt = this.getDb().prepareStatement(sql);
		stmt.setInt(1, id);
		ResultSet result = stmt.executeQuery();
		
		result.next();
		Calendar date = Calendar.getInstance();
		try {
			date.setTimeInMillis(result.getDate("x.calendar_date").getTime());
		} catch (NullPointerException e) {
			// Not an error
		}
		Order order = new Order(result.getInt("x.order_id"), result.getInt("x.customer_id"), result.getInt("x.order_status_id"), date, result.getDouble("x.delivery_charges"));
		
		
		
		// Get LineItems
		sql = "SELECT x.position_id, x.article_id, x.amount, x.price, y.type, y.name, y.price, y.product_key, y.size_byte, y.file_path" +
				" FROM line_items x" +
				" JOIN article y ON (x.article_id = y.article_id)" +
				" WHERE x.order_id = ?";
		
		stmt = this.getDb().prepareStatement("sql");
		stmt.setInt(1, order.getId());
		result = stmt.executeQuery();
		
		Article article = null;
		LineItem item = null;
		
		while(result.next()) {
			if(result.getString("y.type").equals("WH")){
				article = new WarehouseArticle(
						result.getInt("x.article_id"),
						result.getString("y.name"),
						result.getDouble("y.price"));
				
			} else {
				article = new DownloadArticle(
						result.getInt("x.article_id"),
						result.getString("y.name"),
						result.getDouble("y.price"),
						result.getString("y.product_key"),
						result.getLong("y.size_byte"),
						result.getString("y.file_path"));
			}
			item = new LineItem(article, result.getInt("x.position_id"), result.getInt("x.amount"), result.getDouble("x.price"));
			try {
				order.addLine_item(item);
			} catch (PositionIdTakenException e) {
				// Cannot occure
				System.out.println("Fatal Error.\n\n");
				e.printStackTrace();
			}
		}
		
		return order;
	}

	@Override
	public ArrayList<Order> getAll() throws SQLException {
		// Get Order and Order Status
				String sql = "SELECT x.order_id, x.customer_id, x.order_status_id, x.delivery_charges, x.calendar_date, y.name" +
						" FROM orders x" +
						" LEFT JOIN order_status y ON (x.order_status_id = y.order_status_id)";
				
				PreparedStatement stmt = this.getDb().prepareStatement(sql);
				
				ResultSet result = stmt.executeQuery();
				ArrayList<Order> orders = new ArrayList<Order>();
				
				while(result.next()) {
					Calendar date = Calendar.getInstance();
					try {
						date.setTimeInMillis(result.getDate("x.calendar_date").getTime());
					} catch (NullPointerException e) {
						// Not an error
					}
					Order order = new Order(result.getInt("x.order_id"), result.getInt("x.customer_id"), result.getInt("x.order_status_id"), date, result.getDouble("x.delivery_charges"));
					
					// Get LineItems
					sql = "SELECT x.position_id, x.article_id, x.amount, x.price, y.type, y.name, y.price, y.product_key, y.size_byte, y.file_path" +
							" FROM line_item x" +
							" JOIN article y ON (x.article_id = y.article_id)" +
							" WHERE x.order_id = ?";
					
					stmt = this.getDb().prepareStatement(sql);
					stmt.setInt(1, order.getId());
					ResultSet result2 = stmt.executeQuery();
					
					Article article = null;
					LineItem item = null;
					
					while(result2.next()) {
						if(result2.getString("y.type").equals("WH")){
							article = new WarehouseArticle(
									result2.getInt("x.article_id"),
									result2.getString("y.name"),
									result2.getDouble("y.price"));
							
						} else {
							article = new DownloadArticle(
									result2.getInt("x.article_id"),
									result2.getString("y.name"),
									result2.getDouble("y.price"),
									result2.getString("y.product_key"),
									result2.getLong("y.size_byte"),
									result2.getString("y.file_path"));
						}
						item = new LineItem(article,
								result2.getInt("x.position_id"),
								result2.getInt("x.amount"),
								result2.getDouble("x.price"));
						try {
							order.addLine_item(item);
						} catch (PositionIdTakenException e) {
							// Cannot occure
							System.out.println("Fatal Error.\n\n");
							e.printStackTrace();
						}
					}
					orders.add(order);
				}
				
				
				return orders;
	}

	@Override
	public Order add(Order object) throws SQLException {
		String sql_order = "INSERT INTO orders(customer_id, order_status_id, delivery_charges, calendar_date)" +
				" VALUES (?,?,?,?)";
		String sql_line_item = "INSERT INTO line_item(order_id, position_id, article_id, amount, price) VALUES ";
		
		PreparedStatement stmt = this.getDb().prepareStatement(sql_order, PreparedStatement.RETURN_GENERATED_KEYS);
		stmt.setInt(1, object.getCustomer_id());
		stmt.setInt(2, object.getStatus());
		stmt.setDouble(3, object.getDelivery_charges());
		stmt.setDate(4, new java.sql.Date(object.getOrder_date().getTimeInMillis()));
		
		if(stmt.executeUpdate() > 0) {
			ResultSet generated_keys = stmt.getGeneratedKeys();
			if(generated_keys.next())
				object.setId(generated_keys.getInt(1));
		}
		int size = object.getLine_items().size();
		if(size > 0) {
			
			sql_line_item = sql_line_item + "(?,?,?,?,?)";
			for(int x = 1; x<size; x++) {
				sql_line_item = sql_line_item + ",(?,?,?,?,?)";
			}
			
			stmt = this.getDb().prepareStatement(sql_line_item);
			int y = 0;
			int position_id = 0;
			for(LineItem item : object.getLine_items()) {
				y++;
				stmt.setInt(y, object.getId());
				y++;
				position_id++;
				item.setPosition_id(position_id);
				stmt.setInt(y, position_id);
				y++;
				stmt.setInt(y, item.getArticle().getArticleId());
				y++;
				stmt.setInt(y, item.getAmount());
				y++;
				stmt.setDouble(y, item.getPrice());
			}
			stmt.executeUpdate();
		}
		
		
		return object;
	}

	@Override
	public boolean update(Order object) throws SQLException {

		String drop_line_items = "DELETE FROM line_item WHERE order_id = ?";
		String sql_line_item = "INSERT INTO line_item(order_id, position_id, article_id, amount, price)";
		String order = "UPDATE orders SET order_status_id = ?, delivery_charges = ?, calendar_date = ? WHERE order_id = ?";
		
		
		
		PreparedStatement stmt = this.getDb().prepareStatement(drop_line_items);
		stmt.setInt(1, object.getId());	
		stmt.execute();
		
		int size = object.getLine_items().size();
		if(size > 0) {
			
			for(int x = 0; x<size; x++) {
				sql_line_item = sql_line_item + " VALUES (?,?,?,?,?)";
			}
			
			stmt = this.getDb().prepareStatement(sql_line_item);
			int y = 0;
			int position_id = 0;
			for(LineItem item : object.getLine_items()) {
				y++;
				stmt.setInt(1, object.getId());
				y++;
				position_id++;
				item.setPosition_id(position_id);
				stmt.setInt(y, position_id);
				y++;
				stmt.setInt(y, item.getArticle().getArticleId());
				y++;
				stmt.setInt(y, item.getAmount());
				y++;
				stmt.setDouble(y, item.getPrice());
			}
			stmt.executeUpdate();
		}
		
		stmt = this.getDb().prepareStatement(order);
		stmt.setInt(1, object.getStatus());
		stmt.setDouble(2, object.getDelivery_charges());
		stmt.setDate(3, new java.sql.Date(object.getOrder_date().getTimeInMillis()));
		
		stmt.execute();
		
		return true;
	}

}
